import datetime
import sys
import re
import psycopg2


class DbOperate:
    def __init__(self, **dbinfo):
        self.db_info = dbinfo
        self.conn = None
        self.cur = None

    def conn_db(self):
        self.conn = psycopg2.connect(database=self.db_info['db_name'],
                                     user=self.db_info['db_user'],
                                     password=self.db_info['db_password'],
                                     host=self.db_info['db_host'],
                                     port=self.db_info['port'])
        self.conn.set_client_encoding('utf-8')
        self.cur = self.conn.cursor()
        print("connect successful")

    def exec_dml_sql(self, sql_cmd):
        self.cur.execute(sql_cmd)
        self.conn.commit()
        print("dml operate successful")

    def exec_dql_sql(self, sql_cmd):
        self.cur.execute(sql_cmd)
        rows = self.cur.fetchall()
        print("select successful")
        return rows

    def close_conn(self):
        self.conn.close()
        print("close connect successful")


class SqlAttribute:
    def __init__(self, explain_file, time_file):
        self.explain_file = explain_file
        self.time_file = time_file
    
    def get_commit_id(self):
        sql_stmt = []
        sql_explain = []
        explain_dict = {}
        exe_time_arr = []
        stmt_flag = 0
        expl_flag = 0
        with open(self.explain_file) as file:
            for line in file:
                if line.startswith('commitId:'):
                    commit_id = line.strip().split(':')[1]
                elif line.startswith('sql_statement'):
                    if len(sql_stmt) != 0:
                        stmt_str = ''.join(sql_stmt)
                        if len(sql_explain) == 0:
                            explain_str = 'can not execute explain!'
                        else:
                            explain_str = ''.join(sql_explain)
                        explain_dict.update({f'{stmt_str}': f'{explain_str}'})
                    sql_stmt = []
                    sql_explain = []
                    stmt_flag = 1
                    expl_flag = 0
                    continue
                elif line.startswith('sql_explain'):
                    expl_flag = 1
                    stmt_flag = 0
                    continue

                if stmt_flag == 1 and line != "\n":
                    sql_stmt.append(line)
                elif expl_flag == 1 and line != "\n":
                    sql_explain.append(line)
            if len(sql_stmt) != 0:
                stmt_str = ''.join(sql_stmt)
                if len(sql_explain) == 0:
                    explain_str = 'can not execute explain!'
                else:
                    explain_str = ''.join(sql_explain)
                explain_dict.update({f'{stmt_str}': f'{explain_str}'})

        with open(self.time_file) as file:
            for line in file:
                now_time = line.strip().split(':')[-1]
                exe_time_arr.append(now_time)
        return commit_id, explain_dict, exe_time_arr

if __name__ == '__main__':
    Usage = "Usage:input as " \
            "'python3 sqlperf_in_db.py explain_file exetime_file " \
            "scene [db_type = openGauss]' "
    if len(sys.argv) < 4:
        print(Usage)
        exit(0)

    sql_explain_file = sys.argv[1]
    sql_exetime_file = sys.argv[2]
    sql_scene = sys.argv[3]
    if len(sys.argv) == 5:
        sql_db_type = sys.argv[4]
    else:
        sql_db_type = "openGauss"
    sql_atbt = SqlAttribute(sql_explain_file, sql_exetime_file)

    db_info = {'db_name': '',
               'db_user': '',
               'db_password': '',
               'db_host': '',
               'port': ''}
    y_db = DbOperate(**db_info)
    y_db.conn_db()
    today_time = datetime.datetime.now().strftime('%Y-%m-%d')
    already_run = 0

    # get exist res in database, only save three diffrent day
    cmd = f'select start_time from "ReportApp_sqlperfcommit" ' \
          f'where scene=\'{sql_scene}\' and db_type=\'{sql_db_type}\' ' \
          f'order by start_time desc;'
    times = y_db.exec_dql_sql(cmd)

    for time in times:
        cur_time = time[0].strftime('%Y-%m-%d')
        print(cur_time)
        if cur_time == today_time:
            already_run = 1
            break
    if len(times) > 0:
        delete_time = time[0].strftime('%Y-%m-%d')
    if already_run == 1:
        print("today is already run!")
    else:
        print("today is new day!")
        if len(times) == 3:
            cmd = f'delete from "ReportApp_sqlperfcommit" ' \
                  f'where start_time=\'{delete_time}\' ' \
                  f'and scene=\'{sql_scene}\' ' \
                  f'and db_type=\'{sql_db_type}\'; ' \
                  f'delete from "ReportApp_sqlperf" ' \
                  f'where start_time=\'{delete_time}\' ' \
                  f'and scene=\'{sql_scene}\' and db_type=\'{sql_db_type}\'; '
            y_db.exec_dml_sql(cmd)
        thread_num = 1
        commitid, sql_explain_dict, exe_time = sql_atbt.get_commit_id()
        cmd_insert = f'insert into "ReportApp_sqlperfcommit" ' \
                     f'values(\'{today_time}\', \'{commitid}\', ' \
                     f'\'{sql_scene}\', \'{sql_db_type}\'); '
        y_db.exec_dml_sql(cmd_insert)
        num = 0
        for sql_stmt_each, sql_expl in sql_explain_dict.items():
            expl = sql_expl.replace('\'', '\\\'')
            stmt = sql_stmt_each.replace('\'', '\\\'')
            cmd_insert = f'insert into "ReportApp_sqlperf"(sql_statement, ' \
                         f'sql_explain, exe_time, thread_num, start_time, ' \
                         f'scene, db_type) values(e\'{stmt}\', e\'{expl}\', ' \
                         f'\'{exe_time[num]}\', 1, \'{today_time}\', ' \
                         f'\'{sql_scene}\', \'{sql_db_type}\'); '
            print(cmd_insert)
            y_db.exec_dml_sql(cmd_insert)
            num += 1

    y_db.close_conn()
